import { EggLogger } from 'egg';
import { Inject, HTTPController, HTTPMethod, HTTPMethodEnum, Context, EggContext, Middleware, HTTPBody, HTTPQuery } from '@eggjs/tegg';
import { responseFormatter } from 'app/middleware/responseFormatter';
import { AddBordOptions, UpdateBordOptions } from 'typings/app/bord';
import { EggAppConfig } from 'typings/app';
// const sql = require('mssql');
import * as sql from 'mssql';
// import { toInt, removeUndefinedValues } from 'app/libs/utils';

@HTTPController({
  path: '/bord',
})
@Middleware(responseFormatter)
export class BordController {
  @Inject()
  logger: EggLogger;
  @Inject()
  config: EggAppConfig;

  @HTTPMethod({
    method: HTTPMethodEnum.POST,
    path: '/updateBord',
  })
  async updateBord(@Context() ctx: EggContext, @HTTPBody() bord: UpdateBordOptions) {
    await ctx.model.Bord.update(bord, {
      where: { id: bord.id },
    });
    return {};
  }

  @HTTPMethod({
    method: HTTPMethodEnum.DELETE,
    path: '/deleteBord',
  })
  async deleteBord(@Context() ctx: EggContext, @HTTPQuery() id: number) {
    const bord = await ctx.model.Bord.findByPk(id);
    await bord.destroy();
    return {};
  }

  @HTTPMethod({
    method: HTTPMethodEnum.POST,
    path: '/addBord',
  })
  async addBord(@Context() ctx: EggContext, @HTTPBody() bord: AddBordOptions) {
    const currBord = await ctx.model.Bord.findOne({
      where: { name: bord.name, path: bord.path },
    });
    if (currBord) {
      ctx.throw({
        code: 1001,
        message: '添加失败，当前存在相同路径的看板！',
      });
    }
    await ctx.model.Bord.create(bord);
    return {};
  }
  @HTTPMethod({
    method: HTTPMethodEnum.GET,
    path: '/getTempList2',
  })
  async getTempList2(@Context() ctx: EggContext) {
    const config = {
      server: '192.168.3.52', // MSSQL服务器地址
      authentication: {
        type: 'default',
        options: {
          userName: 'cncpro', // 数据库用户名
          password: 'cncpro', // 数据库密码
        },
      },
      options: {
        database: 'CNCPROCommon', // 要连接的数据库名称
        encrypt: false, // 使用windows azure，需要设置此配置。
      },
    };

    const sqlString = `select a.Device,m.Address,m.Line,Temperature=Convert(varchar,m.Temperature),Humidity=Convert(varchar,m.Humidness),
    RecordDate = Convert(date,case when DATEPART(Hour, m.PustTime) < 8 then DATEADD(day, -1, m.PustTime) else m.PustTime end),
    Shift =case when DATEPART(Hour, m.PustTime)>= 8 and DATEPART(Hour, m.PustTime)< 20 then '1' else '2' end,
    RecorderTime=m.PustTime,a.STemperature,a.SHumidity
      from T_THmachineData m
    left join T_MES_ProEnvironmentAddr a on m.MachineName = a.Device 
    where DATEPART(MINUTE,m.PustTime)=0 and m.PustTime >='" + Bagtime + "' and m.PustTime <= '" + Endtime + "' and a.FactoryName='" + fac + "'`;

    try {
      await sql.connect(config);
      const result = await sql.query(sqlString);
      return result;
    } catch (err) {
      console.log(err);
      ctx.throw({
        code: 1002,
        message: '查询失败！',
      });
      // 错误处理
    }
  }


  @HTTPMethod({
    method: HTTPMethodEnum.GET,
    path: '/getTempList',
  })
  async getTempList(@Context() ctx: EggContext) {
    const config = {
      server: '192.168.3.52', // MSSQL服务器地址
      authentication: {
        type: 'default',
        options: {
          userName: 'cncpro', // 数据库用户名
          password: 'cncpro', // 数据库密码
        },
      },
      options: {
        database: 'CNCPROCommon', // 要连接的数据库名称
        encrypt: false, // 使用windows azure，需要设置此配置。
      },
    };

    const sqlString = `select a.ID,a.Device,a.DeviceName,a.Address,a.Line,a.Handler,a.STemperature,a.SHumidity,a.SIllumination,a.SCleanliness,a.Factory,a.FactoryName,a.DDLink,b.Abnormal,b.LastTime,a.AddPerson,a.AddTime,a.UpdatePerson,a.UpdateTime
    from T_MES_ProEnvironmentAddr a
    left join (select MachineName,Abnormal=convert(bit,case when Temperature<Convert(decimal(18,1),substring(STemperature, 0, CHARINDEX('-', STemperature)))
    or Temperature> Convert(decimal(18, 1), SUBSTRING(STemperature, CHARINDEX('-', STemperature) + 1, LEN(STemperature)))
    or Humidness<Convert(decimal(18,1),substring(SHumidity, 0, CHARINDEX('-', SHumidity)))
    or Temperature> Convert(decimal(18, 1), SUBSTRING(SHumidity, CHARINDEX('-', SHumidity) + 1, LEN(SHumidity))) then 1 else 0 end),
    LastTime = PustTime
    from T_THmachineData
    where Id in (select Max(Id) from T_THmachineData group by MachineName)) b on a.Device = b.MachineName;`;

    try {
      await sql.connect(config);
      const result = await sql.query(sqlString);
      console.log(result);
      // 关闭连接
      sql.close();
      return result;
    } catch (err) {
      console.log(err);
      ctx.throw({
        code: 1002,
        message: '查询失败！',
      });
      // 错误处理
    }
  }
}
